package com.ujiuye.dao;

import com.ujiuye.pojo.Student;
import com.ujiuye.utils.MyUtils;
import com.ujiuye.utils.PageTools;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StudentDao {

    /**
     * 添加学生
     * @param s
     * @return
     */
    public int insertStudent(Student s) {

        String sql = "insert into student values(?,?,?,?,?,?)";
        Object[] params = {null,s.getSname(),s.getSex(),s.getSbir(),s.getHobby(),s.getPhoto()};
        try {

            return MyUtils.qr.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 查询总条数
     * @return
     */
    public int queryCountStudent() {
        String sql = "select count(*) from student";
        try {
            long l = (long)MyUtils.qr.query(sql,new ScalarHandler());
            return (int) l;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 分页查询
     * @param pt
     * @return
     */
    public List<Student> pageQueryStudent(PageTools pt) {
        String sql = "select * from student limit ?,?";
        try {
            return MyUtils.qr.query(sql,new BeanListHandler<>(Student.class),pt.getIndex(),pt.getPageSize());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 删除
     * @param sid
     * @return
     */
    public int deleteStudentBySid(int sid) {
        String sql = "delete from student where sid = ?";
        try {
            return MyUtils.qr.update(sql,sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 修改数据的回显
     * @param sid
     * @return
     */
    public Student queryStudentBySid(int sid) {
        String sql = "select * from student where sid = ?";
        try {
            return MyUtils.qr.query(sql,new BeanHandler<>(Student.class),sid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 确认修改
     * @param s
     * @return
     */
    public int updateStudent(Student s) {
        String sql = "update student set sname = ?,sex = ?,hobby = ?,sbir = ?,photo = ? where sid = ?";
        Object[] params = {s.getSname(),s.getSex(),s.getHobby(),s.getSbir(),s.getPhoto(),s.getSid()};
        try {
            return MyUtils.qr.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 批量删除
     * @param sids
     * @return
     */
    public int batchDeleteStudent(String sids) {
        // 1,2,3  使用字符串的拼接  使用占位符? 会自动在左右两侧加单引号
        //delete from student where sids in(1,2,3)
        //delete from student where sids in('1,2,3')
        String sql = "delete from student where sid in(" + sids + ")";
        try {
            return MyUtils.qr.update(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 批量删除  查询学生信息
     * @param sids
     * @return
     */
    public List<Object> queryStudentBySids(String sids) {
        String sql = "select photo from student where sid in (" + sids + ")";
        try {
            return MyUtils.qr.query(sql,new ColumnListHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
